﻿using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Core.Entity;
using Wedn.Interface.DAL;
using Wedn.Utility;

namespace Wedn.DAL.MSSQL
{
    /// <summary>
    /// 评论表操作类
    /// </summary>
    public class CommentDAO : IComment
    {
        /// <summary>
        /// 向评论表中插入一行数据, 返回执行结果受影响行数
        /// </summary>
        /// <param name="comment">要增加的评论对象</param>
        /// <returns>执行结果受影响行数</returns>
        public int Insert(Comment comment)
        {
            string sql = @"INSERT INTO [&#WednTablePrefix%&_Comments]
                                    (
                                        [comment_post_id],
                                        [comment_user_id],
                                        [comment_auther],
                                        [comment_auther_email],
                                        [comment_auther_url],
                                        [comment_auther_ip],
                                        [comment_pubdate],
                                        [comment_content],
                                        [comment_status],
                                        [comment_parent_id]
                                    )
                                    VALUES
                                    (
                                        @PostID,
                                        @UserId,
                                        @Auther,
                                        @AutherEmail,
                                        @AutherUrl,
                                        @AutherIP,
                                        @PubDate,
                                        @Content,
                                        @Status,
                                        @ParentId
                                    );";
            return ExecuteSql(comment, sql);
        }

        /// <summary>
        /// 根据评论ID删除评论表中的评论记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">要删除的评论ID</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int id)
        {
            string sql = "DELETE FROM [&#WednTablePrefix%&_Comments] WHERE [comment_id]=@Id;";
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Id", id));
        }

        /// <summary>
        /// 根据评论ID数组删除评论表中的评论记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="ids">要删除的评论ID数组</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int[] ids)
        {
            //拼接要删除的ID
            string ps = string.Join(",", ids);
            string sql = "DELETE FROM [&#WednTablePrefix%&_Comments] WHERE [comment_id] IN (" + ps + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 根据评论状态删除评论表中的评论记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="status">要删除的评论的状态(0:待审核,1:审核通过,2:回收站,3:垃圾评论)</param>
        /// <returns>执行结果受影响行数</returns>
        public int DeleteByStatus(int status)
        {
            string sql = "DELETE FROM [&#WednTablePrefix%&_Comments] WHERE [comment_status] = @Status;";
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Status", status));
        }

        /// <summary>
        /// 更新一个完整评论对象, 返回执行结果受影响行数
        /// </summary>
        /// <param name="comment">更新的评论对象</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateComment(Comment comment)
        {
            string sql = @"UPDATE [&#WednTablePrefix%&_Comments]
                                   SET
                                        [comment_post_id]=@PostID,
                                        [comment_user_id]=@UserId,
                                        [comment_auther]=@Auther,
                                        [comment_auther_email]=@AutherEmail,
                                        [comment_auther_url]=@AutherUrl,
                                        [comment_auther_ip]=@AutherIP,
                                        [comment_pubdate]=@PubDate,
                                        [comment_content]=@Content,
                                        [comment_status]=@Status,
                                        [comment_parent_id]=@ParentId
                                    WHERE 
                                        [comment_id]=@Id";
            return ExecuteSql(comment, sql);
        }

        /// <summary>
        /// 根据评论ID数组批量更新多个评论状态, 返回执行结果受影响行数
        /// </summary>
        /// <param name="ids">评论ID数组</param>
        /// <param name="status">更新的状态(0:待审核,1:审核通过,2:回收站,3:垃圾评论)</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateStatus(int[] ids, int status)
        {
            //拼接要更新的ID
            string ps = string.Join(",", ids);
            string sql = @"UPDATE [&#WednTablePrefix%&_Comments]
                                    SET
                                        [comment_status]=@Status
                                    WHERE 
                                        [comment_id] in (" + ps + ");";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Status", status));
        }

        /// <summary>
        /// 获取表中主键最大值, 返回主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public int SelectMaxId()
        {
            return SqlHelper.SelectMaxId("comment_id", "&#WednTablePrefix%&_Comments");
        }

        /// <summary>
        /// 选取评论总数
        /// </summary>
        /// <param name="where">选择条件(忽略则传入null或空值)</param>
        /// <returns>总数</returns>
        public int SelectCommentsCount(string where)
        {
            string sql = "SELECT COUNT (1) FROM [&#WednTablePrefix%&_Comments] WHERE {0};";
            sql = string.Format(sql, string.IsNullOrEmpty(where) ? string.Empty : where);
            return (int)SqlHelper.ExecuteScalar(sql);
        }

        /// <summary>
        /// 选取评论总数
        /// </summary>
        /// <param name="where">选择条件(忽略则传入null或空值)</param>
        /// <returns>总数</returns>
        private int SelectCommentsCount(string where, SqlParameter[] parmeters)
        {
            string sql = "SELECT COUNT (1) FROM [&#WednTablePrefix%&_Comments] WHERE {0};";
            sql = string.Format(sql, string.IsNullOrEmpty(where) ? string.Empty : where);
            return (int)SqlHelper.ExecuteScalar(sql, parmeters);
        }

        /// <summary>
        /// 获取评论表中所有评论信息对象, 返回对象信息集合, 如果不存在则返回null
        /// </summary>
        /// <returns>所有评论对象信息集合</returns>
        public List<Comment> SelectCommentList()
        {
            string sql = @"SELECT
                                        [comment_id],
                                        [comment_post_id],
                                        [comment_auther],
                                        [comment_auther_email],
                                        [comment_auther_url],
                                        [comment_auther_ip],
                                        [comment_pubdate],
                                        [comment_content],
                                        [comment_status],
                                        [comment_parent_id],
                                        [comment_user_id]
                                    FROM [&#WednTablePrefix%&_Comments]";
            return Select(sql);
        }

        /// <summary>
        /// 根据文章ID及页码从评论表中获取指定条数评论信息, 返回评论信息对象集合, 如果不存在则返回null
        /// </summary>
        /// <param name="status">状态(0:待审核,1:审核通过,2:回收站,3:垃圾评论)(忽略该条件则传入-1)</param>
        /// <param name="postId">文章ID(忽略该条件则传入-1)</param>
        /// <param name="pageIndex">页码(忽略该条件则传入-1)</param>
        /// <param name="pageSize">显示条数(忽略该条件则传入-1)</param>
        /// <returns>符合条件评论信息对象集合</returns>
        public List<Comment> SelectCommentList(int status, int postId, int pageIndex, int pageSize)
        {
            string col = @"[comment_id],
                                    [comment_post_id],
                                    [comment_user_id],
                                    [comment_auther],
                                    [comment_auther_email],
                                    [comment_auther_url],
                                    [comment_auther_ip],
                                    [comment_pubdate],
                                    [comment_content],
                                    [comment_status],
                                    [comment_parent_id]";
            if (status != -1)
            {
                string where = " [comment_status]=@Status ";
                string sql = SqlHelper.MakePagedSql("&#WednTablePrefix%&_Comments", col, pageSize, pageIndex, "comment_pubdate", 0, where);
                return Select(sql, new SqlParameter("@Status", status));
            }
            else
            {
                string sql = SqlHelper.MakePagedSql("&#WednTablePrefix%&_Comments", col, pageSize, pageIndex, "comment_pubdate", 0, null);
                return Select(sql);
            }
        }

        /// <summary>
        /// 根据筛选条件从评论表中获取指定条数评论信息, 返回评论信息对象集合
        /// 如果指定int类型参数传入-1, string传入null, 则表示忽略该检索条件
        /// 将默认按照发布时间降序排序, 返回评论信息对象集合, 不存在则返回NULL
        /// </summary>
        /// <param name="keyword">关键词(忽略该条件则传入null)</param>
        /// <param name="status">状态(0:待审核,1:审核通过,2:回收站,3:垃圾评论)(忽略该条件则传入-1)</param>
        /// <param name="pageSize">文章数目(忽略该条件则传入-1)</param>
        /// <param name="pageIndex">页码(忽略该条件则传入-1)</param>
        /// <param name="orderField">排序字段(忽略该条件则传入null)</param>
        /// <param name="order">排序方式(0:降序(desc)|1:升序(asc))(忽略该条件则传入-1)</param>
        /// <param name="recordCount">返回查询到的记录数</param>
        /// <returns>符合条件评论信息对象集合</returns>
        public List<Comment> SelectCommentList(string keyword, int status, int pageIndex, int pageSize, string orderField, int order, out int recordCount)
        {
            List<string> conditions = new List<string>();
            List<SqlParameter> ps = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(keyword))
            {
                conditions.Add(string.Format(" [comment_auther] LIKE '%{0}%' OR [comment_content] LIKE '%{0}%' ", keyword));
            }
            if (status != -1)
            {
                conditions.Add(" [comment_status]=@Status ");
                ps.Add(new SqlParameter("@Status", status));
            }
            string where = conditions.Count > 0 ? string.Join(" AND ", conditions.ToArray()) : null;
            recordCount = SelectCommentsCount(where, ps.ToArray());
            string col = @"[comment_id],
                                    [comment_post_id],
                                    [comment_user_id],
                                    [comment_auther],
                                    [comment_auther_email],
                                    [comment_auther_url],
                                    [comment_auther_ip],
                                    [comment_pubdate],
                                    [comment_content],
                                    [comment_status],
                                    [comment_parent_id]";
            string sql = SqlHelper.MakePagedSql("&#WednTablePrefix%&_Comments", col, pageSize, pageIndex, orderField, order, where);
            return Select(sql, ps.ToArray());
        }

        /// <summary>
        /// 根据评论ID获取评论表中所有评论对象, 返回评论对象, 如果不存在则返回null
        /// </summary>
        /// <param name="id">评论ID</param>
        /// <returns>评论对象</returns>
        public Comment SelectComment(int id)
        {
            string sql = @"SELECT TOP 1
                                        [comment_id],
                                        [comment_post_id],
                                        [comment_user_id],
                                        [comment_auther],
                                        [comment_auther_email],
                                        [comment_auther_url],
                                        [comment_auther_ip],
                                        [comment_pubdate],
                                        [comment_content],
                                        [comment_status],
                                        [comment_parent_id]
                                    FROM [&#WednTablePrefix%&_Comments]";
            List<Comment> list = Select(sql, new SqlParameter("@Id", id));
            return list != null ? list[0] : null;
        }
        #region 本类私有方法

        #region ExecuteNonQuery
        /// <summary>
        /// 执行写入数据库操作
        /// </summary>
        /// <param name="comment">要写入数据库的对象</param>
        /// <param name="sql">数据库的写入语句</param>
        /// <returns>返回受影响行数</returns>
        private static int ExecuteSql(Comment comment, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                   new SqlParameter("@PostID", comment.PostId),
                   new SqlParameter("@UserId", comment.UserId),
                   new SqlParameter("@Auther", comment.Auther),
                   new SqlParameter("@AutherEmail", string.IsNullOrEmpty(comment.AutherEmail) ? System.DBNull.Value : (object)comment.AutherEmail),
                   new SqlParameter("@AutherUrl", string.IsNullOrEmpty(comment.AutherUrl) ? System.DBNull.Value : (object)comment.AutherUrl),
                   new SqlParameter("@AutherIP", string.IsNullOrEmpty(comment.AutherIp) ? System.DBNull.Value : (object)comment.AutherIp),
                   new SqlParameter("@PubDate", comment.PubDate),
                   new SqlParameter("@Content", comment.Content),
                   new SqlParameter("@Status", comment.Status),
                   new SqlParameter("@ParentId", comment.ParentId),
                   new SqlParameter("@Id", comment.Id));
        }
        #endregion

        #region Select
        /// <summary>
        /// 选取数据集合方法, 如果不存在则返回null
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameter">参数列表</param>
        /// <returns>数据集合</returns>
        private static List<Comment> Select(string sql, params SqlParameter[] parameter)
        {
            List<Comment> list = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                if (reader.HasRows)
                {
                    list = new List<Comment>();
                    while (reader.Read())
                    {
                        Comment comment = new Comment()
                        {
                            Id = reader.GetInt32(0),
                            PostId = reader.GetInt32(1),
                            UserId = reader.GetInt32(10),
                            Auther = reader.GetString(2),
                            AutherEmail = reader.IsDBNull(3) ? null : reader.GetString(3),
                            AutherUrl = reader.IsDBNull(4) ? null : reader.GetString(4),
                            AutherIp = reader.IsDBNull(5) ? null : reader.GetString(5),
                            PubDate = reader.GetDateTime(6),
                            Content = reader.GetString(7),
                            Status = reader.GetInt32(8),
                            ParentId = reader.GetInt32(9)
                        };
                        list.Add(comment);
                        #region 不用字段索引
                        //Convert.ToInt32(reader["comment_id"]),
                        //Convert.ToInt32(reader["comment_post_id"]),
                        //Convert.ToString(reader["comment_auther"]),
                        //reader["comment_auther_email"] != null ? Convert.ToString(reader["comment_auther_email"]) : string.Empty,
                        //reader["comment_auther_url"] != null ? Convert.ToString(reader["comment_auther_url"]) : string.Empty,
                        //reader["comment_auther_ip"] != null ? Convert.ToString(reader["comment_auther_ip"]) : string.Empty,
                        //Convert.ToDateTime(reader["comment_pubdate"]),
                        //Convert.ToString(reader["comment_content"]),
                        //Convert.ToInt32(reader["comment_status"]),
                        //Convert.ToInt32(reader["comment_parent_id"]),
                        //Convert.ToInt32(reader["comment_user_id"])); 
                        #endregion
                    }
                }
            }
            return list;
        }
        #endregion

        #endregion
    }
}
